-- @author lixiyong01
-- @date 2023.05.11
-- 健康分报警工单
-- 2023.08.19 调整优化

use hdp_lbg_supin_zplisting;

add jar viewfs://58-cluster/home/hdp_lbg_supin/resultdata/offline_warehouse3/udf/warehouse_tool-1.0.18.1-shaded.jar;
create temporary function SendOndutyUDF AS 'com.bj58.adsp.dp.hive.offline_udf.SendOndutyUDF';

-- SendOndutyUDF： eventId,subject,description,responser,severity,rgId,ctiId,agentId,agentSecret,notifyRg,notifyAdmin
insert overwrite table app_zp_rock_health_score_ticket_2onduty partition (dt=${dateSuffix})
select
	event_id,
	SendOndutyUDF(
	    event_id, subject, detail, responser, 3,
        'rg-20211215174331161wequ39',
        '20230619113249249h86n13',
        '2e4d22cf-87e6-4b80-8af1-fcbc095c63db',
        '000000', 0, 1
    ) as send_result,
    responser,
    subject,
    detail
from (
    select event_id, responser, subject,
        concat(
            description, ' 健康分介绍：https://docs.qq.com/doc/DSGJaZFZhVXpvbGFS'
        ) as detail
    from (
        select
            concat(from_unixtime(unix_timestamp(),'yyyyMMddHHmmss'),cast(rand() * 100000000 as int)) as event_id,
            owner_id as responser,
            '健康分过低告警' as subject,
            concat('数据管理提醒您，您的健康分低于85分，请处理：',health_score) as description
        from (
            select team, owner_id,
                concat_ws('，',collect_list(
                    concat(
                        if(tag='store','存储健康分：','计算健康分：'), cast(total_score as int),'分', '，请查看：',help_link
                    )
                )) as health_score
            from (
                select tag, team, owner_id, total_score,
                    concat('http://star-zp.58corp.com/sre/index.html#/star/cost/health_score_list2/',tag,'/',dt,'/0/',owner_id) as help_link
                from hdp_lbg_supin_zplisting.app_zp_rock_health_score_cube
                where dt='${#date(0, 0, -1):yyyyMMdd#}'
                and dimension='tag,team,owner_id'
                and total_score<85
            ) t0
            group by team, owner_id
        ) t1
        where team like '%技术支撑线%'
    ) t2
    limit 30
) t;






